package com.lingyun.ucm.web.mvc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.lingyun.ucm.common.pojo.bean.ConfigAcquireFailLog;

/**
 * 
 * @author Terrence2013
 * 2018年1月24日 上午9:53:30
 * 读取配置失败记录Dao
 */
public class ConfigAcquireFailLogDao{

    /**
     * 创建config_acquire_fail_log表
     *
     * @return
     */
    public static boolean createTable() {
        Statement statement = null;
        Connection conn = null;
        boolean result = false;
        try {
            conn = H2Util.getConnection();
            conn.setAutoCommit(false);
            statement = conn.createStatement();
            statement.execute("DROP TABLE IF EXISTS CONFIG_ACQUIRE_FAIL_LOG");
            result = statement.execute("CREATE TABLE CONFIG_ACQUIRE_FAIL_LOG(ID LONG IDENTITY(1,1) PRIMARY KEY,IP VARCHAR(20),FAIL_MESSAGE VARCHAR(100),FAIL_TIME DATETIME)");
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 插入记录
     *
     * @param configAcquireFailLog
     * @return
     */
    public int insert(ConfigAcquireFailLog configAcquireFailLog) {
        PreparedStatement statement = null;
        Connection conn = null;
        int result = 0;
        try {
            conn = H2Util.getConnection();
            conn.setAutoCommit(false);
            statement = conn.prepareStatement("INSERT INTO CONFIG_ACQUIRE_FAIL_LOG (IP,FAIL_MESSAGE,FAIL_TIME) VALUES (?,?,?)");
            statement.setString(1, configAcquireFailLog.getIp());
            statement.setString(2, configAcquireFailLog.getFail_message());
            statement.setTimestamp(3, new Timestamp(new java.util.Date().getTime()));
            result = statement.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }


    /**
     * 查找所有记录
     *
     * @return
     */
    public List<ConfigAcquireFailLog> listAll() {
        PreparedStatement statement = null;
        Connection conn = null;
        ResultSet resultSet = null;
        List<ConfigAcquireFailLog> reportList = new ArrayList<>();
        try {
            conn = H2Util.getConnection();
            statement = conn.prepareStatement("SELECT * FROM CONFIG_ACQUIRE_FAIL_LOG ORDER BY ID DESC ");
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                ConfigAcquireFailLog configAcquireFailLog = new ConfigAcquireFailLog();
                configAcquireFailLog.setId(resultSet.getLong(1));
                configAcquireFailLog.setIp(resultSet.getString(2));
                configAcquireFailLog.setFail_message(resultSet.getString(3));
                configAcquireFailLog.setFail_time(resultSet.getTimestamp(4));
                reportList.add(configAcquireFailLog);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return reportList;
    }

    public int count() {
        PreparedStatement statement = null;
        Connection conn = null;
        ResultSet resultSet = null;
        int num = 0;
        try {
            conn = H2Util.getConnection();
            statement = conn.prepareStatement("SELECT COUNT(1) FROM CONFIG_ACQUIRE_FAIL_LOG");
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                num = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return num;
    }
}
